#! /bin/bash

# 同步前检查主从 gtid 模式是否相同 select  @@gtid_mode;
# 主库连接信息
master_host='10.0.0.63'
master_port=3306
master_user='dba_qianyi_m'
master_pwd='123456'

# 从库连接信息
slave_host='10.0.0.64'
slave_port=3306
slave_user='dba_qianyi_s'
slave_pwd='123456'

# 复制账号
replic_host='10.0.0.64'
replic_user='repl_defds'
replic_pwd='123456'

# gtid 复制: ON/OFF
gtid_replic='OFF'

# 复制线程数：1 ~ CPU*2
replic_threads_num=20

# 延迟复制: 秒/单位 28800=8小时
replic_delay_time=0

# mysql bin 路径: 不同版本 dump 时可能存在错误 
mysql_bin_dir='/usr/local/mysql/mysql57/bin'


# 主库 创建迁移所需用户
# create user dba_qianyi_m@'%' identified by '123456'; grant all on *.* to dba_qianyi_m@'%' with grant option; flush privileges;



# 从库 创建迁移所需用户
# create user dba_qianyi_s@'%' identified by '123456'; grant all on *.* to dba_qianyi_s@'%' with grant option; flush privileges;



# 日志重定向
log_redirection="/root/replic_$(date "+%Y-%m-%d-%H%M").log"

# mysql 连接
mysql_master_conn="$mysql_bin_dir/mysql -h$master_host -P$master_port -u$master_user -p$master_pwd"
mysql_slave_conn="$mysql_bin_dir/mysql  -h$slave_host  -P$slave_port  -u$slave_user  -p$slave_pwd"

mysqldump_conn="$mysql_bin_dir/mysqldump -h$master_host -P$master_port -u$master_user -p$master_pwd"

log_message(){
    local message="$1"
    local status="$2"
    echo "$(date "+%Y-%m-%d %H:%M:%S") ${status} ${message}" >>${log_redirection}
    echo "$(date "+%Y-%m-%d %H:%M:%S") ${status} ${message}"
}



# 检查
check() {
  # 检查命令是否存在
  if [ ! -d "$mysql_bin_dir" ];then
    log_message "$mysql_bin_dir 命令路径不存在" "error"
    exit 1
  fi

  # 测试 master 连通性
  ${mysql_master_conn}  -e 'select 1' >/dev/null 2>&1
  if [ $? -ne 0 ];then 
    log_message "master 连接失败 请检查网络及用户密码" "error" 
    exit 1
  fi

  # 测试 slave 连通性
  ${mysql_slave_conn} -e 'select 1' >/dev/null 2>&1
  if [ $? -ne 0 ];then 
    log_message "slave 连接失败 请检查网络及用户密码"  "error"
    exit 1
  fi

  # 检查 server id  是否相同
  diff_server_id_master=$(${mysql_master_conn} -N  -e 'select @@server_id' 2>/dev/null)
  diff_server_id_slave=$(${mysql_slave_conn}   -N  -e 'select @@server_id' 2>/dev/null)

  if [[ $diff_server_id_master == $diff_server_id_slave ]];then
    log_message "主从 server_id 配置相同" "error"
    exit 1
  fi

  # 检查 gtid 模式是否相同
  master_gtid="$($mysql_master_conn -N -e "select @@gtid_mode" 2>/dev/null)"
  slave_gtid="$($mysql_slave_conn  -N -e "select @@gtid_mode" 2>/dev/null)"
  
  if [[ $master_gtid != $slave_gtid ]];then
   log_message "主从 gtid_mode 不同 请修改为相同参数" "error"
   exit 1
  fi

  # 检查从库是否存在复制信息
  slave_status=$(${mysql_slave_conn} -N  -e 'show slave status' 2>/dev/null)
  if [ -n "$slave_status" ]; then
    log_message "从库存在主从信息 请检查后清除信息再试" "error"
    exit 1
  fi

  # 检查从库是否存在非系统库
  other_database=$(${mysql_slave_conn} -N  -e "SELECT TABLE_SCHEMA FROM information_schema.tables WHERE TABLE_SCHEMA NOT IN ('sys', 'information_schema', 'performance_schema', 'mysql')" 2>/dev/null)
  if [ -n "$other_database" ]; then
    log_message "从库存在非系统库 请检查后清除信息再试"  "error"
    exit 1
  fi
  log_message "同步检查已完成" "info"
}
check

create_replic_user() {
  # 创建复制用户
  ${mysql_master_conn} -e "create user IF NOT EXISTS ${replic_user}@${replic_host} identified by '${replic_pwd}';" >/dev/null 2>&1
  if [ $? -ne 0 ]; then
    log_message "创建复制用户失败 请检查权限" "error"
    exit 1
  fi
  ${mysql_master_conn} -e "grant replication slave on *.* to ${replic_user}@${replic_host};" >/dev/null 2>&1
  if [ $? -ne 0 ]; then
    log_message "创建复制用户失败 请检查权限" "error"
    exit 1
  fi
  ${mysql_master_conn} -e "flush privileges;" >/dev/null 2>&1
  log_message "复制用户已创建" "info"

}
create_replic_user

import_data(){
  log_message  "数据导入中···" "info"
  slave_gtid="$($mysql_slave_conn  -N -e "select @@gtid_mode" 2>/dev/null)"
  ${mysql_master_conn} -N  -e 'flush logs'  >/dev/null 2>&1
  ${mysql_slave_conn} -N  -e 'flush logs'  >/dev/null 2>&1
  # 导入数据至从库
  $mysqldump_conn   --set-gtid-purged=${slave_gtid}  -A  --master-data=1 --single-transaction  -R -E --triggers 2>>${log_redirection}|$mysql_slave_conn  2>>${log_redirection}
  if [[ ${PIPESTATUS[0]} -ne 0 || ${PIPESTATUS[1]} -ne 0 ]]; then
    log_message  "数据导入失败 请检查错误信息" "error"
    exit 1
  fi
}
import_data

config_master_info() {
  log_message "配置复制信息中···" "info"
  if [ ${gtid_replic} == 'OFF' ];then
    $mysql_slave_conn -e "change master to master_host='${master_host}', master_port=${master_port}, master_user='${replic_user}', master_password='${replic_pwd}',  master_delay=${replic_delay_time};" >>${log_redirection} 2>&1
    if [ $? -ne 0 ]; then
      log_message "主从信息配置失败" "error"
      exit 1
    fi
  elif [ ${gtid_replic} == 'ON' ];then
    $mysql_slave_conn -e "change master to master_host='${master_host}', master_port=${master_port}, master_user='${replic_user}', master_password='${replic_pwd}',  master_delay=${replic_delay_time}, master_auto_position=1;" >>${log_redirection} 2>&1
    if [ $? -ne 0 ]; then
      log_message "主从信息配置失败" "error"
      exit 1
    fi
  fi
}

config_master_info

multi_thread_replic(){
  $mysql_slave_conn -e "set global slave_parallel_workers=${replic_threads_num}" &>/dev/null 
}
multi_thread_replic

start_replic_and_check(){
  $mysql_slave_conn -e "start slave" >>${log_redirection} 2>&1
  sleep 5
  replic_status=$($mysql_slave_conn -e "show slave status\G" 2>/dev/null|egrep  "Slave_IO_Running|Slave_SQL_Running"|grep -ic  'no')
  if [ ${replic_status} -eq 1 ]; then
    log_message "主从状态异常 请检查" "error"
    exit 1
  fi
  # upgrade=FORCE  8.0升级后第一次启动
  log_message "跨版本导入时5.7 请执行 mysql_upgrade 后在启动" "info"
  log_message "跨版本导入时8.0 请添加 upgrade=FORCE 参数后在启动" "info"
  log_message "主从配置完成!!! 请在主库删除 ${master_user} 用户" "info"

}
start_replic_and_check

